/**
 * @author AnnaG
 *
 */
package db;


import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.xml.parsers.ParserConfigurationException;
import org.xml.sax.SAXException;
import runnable_functions.ReferedObject;



import details.*;


public class DB implements DataBase {

	private String host_name;
	private String port;
	private String schema_name;
	private String user_name;
	private String user_pass;
	private Connection	conn;

	private static int BATCH_SIZE = 1000;


	private void getConnDataFromFile() throws IOException
	{

		BufferedReader reader = new BufferedReader(new FileReader("ConnConfig.txt"));
		String line;

		while ((line = reader.readLine()) != null) 
		{
			if (line.startsWith("host_name="))
			{
				host_name = line.substring(line.indexOf("=")+1);
			}

			else if(line.startsWith("port="))
			{
				port = line.substring(line.indexOf("=")+1);
			}

			else if (line.startsWith("schema_name="))
			{
				schema_name = line.substring(line.indexOf("=")+1);
			}


			else if (line.startsWith("user_name="))
			{
				user_name = line.substring(line.indexOf("=")+1);
			}


			else if (line.startsWith("user_pass="))
			{
				user_pass = line.substring(line.indexOf("=")+1);
			}

			else if (line.startsWith("//"))
			{
				//this is comment => skip
				continue;
			}

			else
			{
				// this file's format isn't correct
				System.out.println("File ConnConfig.txt doesn't contain connection data");
			}
		}

	}
	public DB() throws IOException
	{
		getConnDataFromFile();
		this.conn = null;
	}



	///////////////////////////////////////////////////////////////////
	/////////////////       SEARCH  METHODS      //////////////////////
	///////////////////////////////////////////////////////////////////


	private int getGenreIDFromDISCID(String discID)
	{
		return Integer.parseInt(discID.substring(discID.indexOf('&')+1));
	}

	//==================================================================================


	/**
	 * DISCID includes genre id
	 * @return - returns CD title, genre name, length, it's track list 
	 * (+ track numbers, lengths, tracks titles and tracks performers in case it's various artists cd), performer, year
	 * @throws - throws SQLException 
	 * 
	 */
	public synchronized Cd searchBy_DISCID(String DISCID) throws SQLException
	{

		int cdNumOfViews = getNumOfViews(DISCID, Target.CD);
		int cdNumOfLikes = getNumOfLikes(DISCID, Target.CD);



		ArrayList<Track> tracks = getCDTracks(DISCID);

		Cd cd = null;

		int genre_id = getGenreIDFromDISCID(DISCID);


		String query = 

				"SELECT   cdTitle, cdLengthInSec, cdYear, " +
						" cdPerformer , genreName, artistName " +

				" FROM Cds, Genres, Artists " +

				" WHERE Cds.DISCID =  ? " + 

				" AND Genres.genreID = ? "
				+ " AND Artists.artistID = Cds.cdPerformer ";


		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		pstmt.setString(1, DISCID);
		pstmt.setInt(2, genre_id); 

		ResultSet	rs		=	pstmt.executeQuery();

		if (rs.first() == true)
		{


			cd=new Cd(rs.getString("cdTitle"), DISCID, rs.getInt("cdPerformer"), rs.getString("artistName"), 
					rs.getInt("cdYear"), rs.getInt("cdLengthInSec"),  rs.getString("genreName"), cdNumOfLikes, cdNumOfViews);

			cd.setTracks(tracks);

		}


		// closing
		rs.close();
		pstmt.close();




		return cd;



	}


	//==================================================================================


	private int getTrackNumFromTrackID(String trackID)
	{
		return Integer.parseInt((trackID.substring(trackID.indexOf('-')+1)));

	}



	//==================================================================================

	/**
	 * 
	 * @return CD's Tracks
	 * @throws SQLException
	 */
	public synchronized ArrayList<Track> getCDTracks(String DISCID) throws SQLException
	{
		ArrayList<Track> tracks = new ArrayList<Track>();
		Track trck;


		String query = 
				"SELECT  TrackID, songName, artistName, Tracks.songID, Tracks.trackLengthInSec " +
						" FROM Songs, Artists, Tracks " +
						" WHERE Tracks.discID = ? " +
						" AND Tracks.songID = Songs.songID " +
						" AND Artists.artistID = Songs.artist_ID ";


		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		pstmt.setString(1, DISCID);

		ResultSet	rs		=	pstmt.executeQuery();

		while (rs.next() == true)
		{
			int trckNum = getTrackNumFromTrackID(rs.getString("TrackID"));

			trck = new Track(DISCID,trckNum, rs.getString("songName"), 
					rs.getString("artistName"), rs.getInt("trackLengthInSec"),rs.getInt("songID"));
			tracks.add(trck);			

		}


		// closing
		rs.close();
		pstmt.close();


		return tracks;

	}


	//==================================================================================

	
	/**
	 * @return - return Song - contains song name , artist , numOfViews, comments, numOfLikes and lyrics of that song
	 * @throws - throws SQLException 
	 * 
	 */
	public synchronized Song searchBySongID(int songID) throws SQLException
	{


		int NumOfSongLikes = getNumOfLikes(Integer.toString(songID), Target.SONG);
		int numOfSongViews = getNumOfViews(Integer.toString(songID), Target.SONG);

		ArrayList<Comment> song_comments = getSongComments(songID);

		Song song = null;


		String query1 ="SELECT  songName, artistName " +
				" FROM Songs, Artists " +
				" WHERE Artists.artistID = Songs.artist_ID AND Songs.songID = ? " ;


		PreparedStatement	pstmt	=	conn.prepareStatement(query1);
		pstmt.setInt(1, songID);
		ResultSet	rs		=	pstmt.executeQuery();



		if (rs.first() == true)
		{

			song = new Song(songID, rs.getString("songName"), rs.getString("artistName"),null, song_comments, NumOfSongLikes, numOfSongViews);

		}



		String query2 ="SELECT  lyrics " +
				" FROM Lyrics " +
				" WHERE Lyrics.songID = ? ";

		pstmt	=	conn.prepareStatement(query2);
		pstmt.setInt(1, songID);


		rs		=	pstmt.executeQuery();



		if (rs.first() == true)
		{
			String lyrics = rs.getString("lyrics");     // = null in case there are no lyrics in data base
			song.setLyric(lyrics);

		}

		// closing
		rs.close();
		pstmt.close();

		return song;

	}


	//=====================================================================================================



	/**
	 * @return - returns Cds of that artist (as performer) - it's DISCID,  title, genre, year, length, artist-name
	 * and numOfLikes, numOfViews of that artist
	 * @throws - throws SQLException 
	 * 
	 */
	public synchronized Artist searchByArtistID(int artistID) throws SQLException 

	{

		int numOfArtistLikes = getNumOfLikes(Integer.toString(artistID), Target.ARTIST);
		int numOfArtistViews = getNumOfViews(Integer.toString(artistID), Target.ARTIST);


		ArrayList<Cd> cds = new  ArrayList<Cd>();
		Cd cd = null;
		Artist artist = null;
		String artistName=null;


		String query = 
				"SELECT DISCID, cdTitle, cdLengthInSec, " +
						" cdYear, genreName, artistName " +
						" FROM Cds, Artists, Genres " + 
						" WHERE Artists.artistID = ? " +
						" AND Genres.genreID = Cds.genreID " +
						" AND Cds.cdPerformer = ? " ;
		
		

		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		pstmt.setInt(1, artistID);
		pstmt.setInt(2, artistID);

		ResultSet	rs		=	pstmt.executeQuery();


		if(!rs.first()){
			return null;
		}
		else {
			
			rs.beforeFirst();
		
		while (rs.next() == true)
		{
			artistName = rs.getString("artistName");
			cd=new Cd(rs.getString("cdTitle"), rs.getString("DISCID"),artistID, artistName, 
					rs.getInt("cdYear"), rs.getInt("cdLengthInSec"), rs.getString("genreName"), 0, 0);
			// we don't need here the numOfLikes and numOfViews of CD (only artist)


			cds.add(cd);
		}

		artist = new Artist(artistID, artistName,numOfArtistLikes, numOfArtistViews, cds);
		}
		// closing
		rs.close();
		pstmt.close();




		
		// since all cds are of the same artist (that was the purpose of the search), we can get it's name from one of the cds


		return artist;
	}

	//==================================================================================



	// if user wants exact search of artist - first find it (and get its id) and then call SearchByArtistID to get more info


	/**
	 * @return - returns top nomOfResults of Artists (names and IDs)
	 * @throws - throws SQLException 
	 */
	public synchronized ArrayList<Artist> searchByArtist(String artist_Name, int nomOfResults) throws SQLException {


		ArrayList<Artist> Artists = new ArrayList<Artist>();
		Artist artist;

		artist_Name = artist_Name.replaceAll("'", "''");	
		String query ="SELECT  artistID, artistName " +
				" FROM Artists " +
				" WHERE artistName LIKE ? " +
				" GROUP BY artistID "+
				"LIMIT 0,"+ nomOfResults;
		
		System.out.println(query);

		
		PreparedStatement	pstmt	=	conn.prepareStatement(query);

		pstmt.setString(1, "%" + artist_Name + "%");


		ResultSet	rs		=	pstmt.executeQuery();



		while (rs.next() == true)
		{

			artist = new Artist(rs.getInt("artistID"), rs.getString("artistName"), 0, 0, null);
			// we don't need here cds, views and likes
			Artists.add(artist);

		}

		// closing
		rs.close();
		pstmt.close();

		return Artists;
	}

	//==================================================================================
	
	/**
	 * @return - returns top nomOfResults Cds (DISCID, title, performer, genre, length, year). 
	 * Some of the parameters maybe empty (year = XXXX)
	 * @throws - throws SQLException 
	 * 
	 * this is exact search
	 */



	public synchronized ArrayList<Cd> advanceCDSearch(String cdTitle, String performer, String genre, int yearFrom, int yearTo, int nomOfResults) throws SQLException
	{
		ArrayList<Cd> cds = new  ArrayList<Cd>();
		Cd cd = null;


		int cdTitleIndex = 0;
		int performerIndex = 0;
		int genreIndex = 0;
		int yearIndex = 0;
		int yearIndexFrom = 0;
		int yearIndexTo = 0;

		int index =0;



		String query = 
				"SELECT DISTINCT DISCID, cdTitle, cdLengthInSec, " +
						" cdPerformer, cdYear, genreName, artistName " +
						" FROM Cds, Artists, Genres " + 						
						" WHERE Genres.genreID = Cds.genreID "+
						" AND Artists.artistID = Cds.cdPerformer ";

		
		if (cdTitle != null)
		{
			cdTitle.replaceAll("'", "''");
			query += " AND Cds.cdTitle LIKE ? ";
			cdTitleIndex = ++index;
		

		}

		if (performer != null)
		{
			query += " AND Artists.artistName = ? ";
		
			performerIndex = ++index;
		

		}



		if (genre != null)
		{
			genre.replaceAll("'", "''");
		
			query += " AND Genres.genreName LIKE ? ";
			genreIndex = ++index;
			

		}



		if (yearFrom != 0 && yearTo !=0)
		{
			
			if (yearFrom == yearTo)
			{
			
			query += " AND Cds.cdYear = ? ";
			yearIndex = ++index;
		
			}
			
			else 
			{
				query += " AND Cds.cdYear >= ? ";
				yearIndexFrom = ++index;
			
				query += " AND Cds.cdYear <= ? ";
				yearIndexTo= ++index;
		
			}

		}


		query += " LIMIT 0,"+ nomOfResults;

		

		PreparedStatement	pstmt	=	conn.prepareStatement(query);

	

		if (cdTitleIndex != 0)
		{
			pstmt.setString(cdTitleIndex, "%" + cdTitle + "%");
		
			
		}

		if (performerIndex != 0)
		{
			pstmt.setString(performerIndex, performer);
		}


		if (genreIndex != 0)
		{
			pstmt.setString(genreIndex, "%" +  genre + "%");
		}


		if (yearIndex != 0)
		{
		
			pstmt.setInt(yearIndex, yearFrom);
		}
		
		if (yearIndexFrom != 0 && yearIndexTo!=0)
		{
			
			pstmt.setInt(yearIndexFrom, yearFrom);
			pstmt.setInt(yearIndexTo, yearTo);
		}
		
		

		ResultSet	rs		=	pstmt.executeQuery();


		while (rs.next() == true)
		{


			cd=new Cd(rs.getString("cdTitle"), rs.getString("DISCID"), rs.getInt("cdPerformer"), rs.getString("artistName"), 
					rs.getInt("cdYear"), rs.getInt("cdLengthInSec"), rs.getString("genreName"), 0, 0); 
			// we don't need here views and likes


			cds.add(cd);
		}


		// closing
		rs.close();
		pstmt.close();



		return cds;

	}


	//=====================================================================================================

	

	/**
	 * @return - returns top nomOfResults Songs (lyrics returned only when words!=null). 
	 * Some of the parameters maybe empty (year = XXXX)
	 * @throws - throws SQLException 
	 * 
	 * this is exact search
	 */

	public synchronized ArrayList<Song> advanceSongSearch(String artistName, String song_title, String words, int nomOfResults) throws SQLException
	{
		ArrayList<Song> songs = new ArrayList<Song>();
		Song song =null;



		String query ="SELECT  Songs.songID, Songs.songName, Artists.artistName " ;


		String q1 = " FROM Songs, Artists ";
		String q2 = " WHERE Artists.artistID = Songs.artist_id ";


		if (words != null)



		{
			words = " LIKE '%" + words.replaceAll("'", "''") + "%' ";
			query += ", Lyrics.lyrics ";
			q1 += ", Lyrics ";
			q2+= " AND Lyrics.songID = Songs.songID AND Lyrics.lyrics " + words;
		} 


		query+= q1 + q2;



		if (artistName != null)
		{

			query += " AND Artists.artistName LIKE '%" + artistName.replaceAll("'", "''") + "%' ";

		}

		if (song_title != null)
		{

			query += " AND Songs.songName LIKE '%" + song_title.replaceAll("'", "''") + "%' ";
		}



		query += " LIMIT 0,"+ nomOfResults;




		Statement	stmt	=	conn.createStatement();
		ResultSet	rs		=	stmt.executeQuery(query);


		while (rs.next() == true)
		{
			String l = null;

			if (words != null)
			{
				l = rs.getString("Lyrics.lyrics");
			}


			song = new Song(rs.getInt("Songs.songID"), rs.getString("Songs.songName"), rs.getString("Artists.artistName"),l, null, 0, 0);
			// we don't need here comments, views and likes
			songs.add(song);

		}

		// closing
		rs.close();
		stmt.close();


		return songs;

	}



	//=====================================================================================================



	/////////////////////////////////////////////////////////////////
	/////////////////       EDIT   METHODS     //////////////////////
	/////////////////////////////////////////////////////////////////



	/**
	 * 
	 * in case cd's genre won't be mentioned, genre_dir_name will be written
	 * @throws SAXException 
	 * @throws ParserConfigurationException 
	 * @throws IOException 
	 * @throws IllegalStateException 
	 * @throws InterruptedException 
	 * @throws - throws SQLException 
	 * 
	 */


	public synchronized void updateFromFreeDB(ArrayList<Cd> cds,String dirName) throws SQLException, IllegalStateException, IOException, ParserConfigurationException, SAXException, InterruptedException
	{


		System.out.println(" entering updateFromFreeDB function");

		int numOfCds = cds.size();

		System.out.println(" numOfCds in genre directory " + dirName + " = " + numOfCds);


		String[] genres = new String[numOfCds];
		ArrayList<String> cdPerformers = new ArrayList<String>();
		ArrayList<String>  discIDs = new ArrayList<String>();
		String[] cdTitles = new String[numOfCds];
		int[] cdLengths = new int[numOfCds];
		int[] cdYears = new int[numOfCds];;

		for (int i =0; i< numOfCds; i++)
		{
			genres[i] = cds.get(i).getGenre();

			cdPerformers.add(cds.get(i).getPerformer());

			cdTitles[i] = cds.get(i).getTitle();

			cdLengths[i] = cds.get(i).getLengthInSec();

			cdYears[i] = cds.get(i).getReleaseYear();

		}


		// first - add genres and get their ids

		System.out.println("batching genres in updateFromFreeDB...");



		int[] genreIDs = batchGenres(genres);



		System.out.println("genres batched in updateFromFreeDB");





		//then add cdperformers and get their ids

		System.out.println("batching cdperformers in updateFromFreeDB...");



		int[] cdperformerIDs = batchArtists(cdPerformers);




		System.out.println("cdperformers batched in updateFromFreeDB");



		// now - songs+artists of tracks

		ArrayList<String> trackTitles = new ArrayList<String>();
		ArrayList<String> trackArtists = new ArrayList<String>();
		ArrayList<Integer> trackLengths = new ArrayList<Integer>();
		ArrayList<String> trackIDs = new ArrayList<String>();


		for (int i =0; i< numOfCds; i++)
		{
			ArrayList<Track> tracks = cds.get(i).getTracks();

			for (int j=0; j< tracks.size(); j++)
			{
				trackTitles.add(tracks.get(j).getTitle());
				trackArtists.add(tracks.get(j).getArtistName());
				trackLengths.add(tracks.get(j).getLengthInSec());			

			}
		}


		// add trackArtists and get their ids

		System.out.println("batching track artists in updateFromFreeDB...");


		int[] trackArtistsIDs = batchArtists(trackArtists);



		System.out.println("track artist batched in updateFromFreeDB");




		// then add trackTitles and get their ids


		System.out.println("batching songs in updateFromFreeDB...");



		int[] trackTitlesIDs = batchSongs(trackTitles, trackArtistsIDs);



		System.out.println("songs batched in updateFromFreeDB");




		// then generate new discIDs and add cds 

		for (int i =0; i< numOfCds; i++)
		{
			discIDs.add(cds.get(i).getAlbom_id() + "&" + genreIDs[i]) ;
		}


		System.out.println("batching cds in updateFromFreeDB...");



		batchCDs(discIDs, cdTitles, cdperformerIDs, genreIDs, cdLengths, cdYears);



		System.out.println("cds batched in updateFromFreeDB");





		// now add tracks


		for (int i =0; i< numOfCds; i++)
		{
			ArrayList<Track> tracks = cds.get(i).getTracks();

			for (int j=0; j< tracks.size(); j++)
			{

				trackIDs.add(discIDs.get(i) + "-" +  tracks.get(j).getTrackNum());
			}
		}


		System.out.println("batching tracks in updateFromFreeDB...");



		batchTracks(trackIDs, trackTitlesIDs, trackLengths);


		System.out.println("tracks batched in updateFromFreeDB");


	}




	//==================================================================================


	/**
	 * Add new cd to DB. cd will include all needed data (including track list)
	 * @throws SQLException 
	 * 
	 */
	public synchronized void addCD(Cd cd) throws SQLException 
			

			{

		
		if (cd.getReleaseYear() > 2155 || cd.getReleaseYear() < 1901) 
		{
			cd.setReleaseYear(0);
		}
		
	
		
		// first - add genre and get it's id


		String genre = cd.getGenre();
	

		int genreID = add_Genre_or_Artist(ReferedObject.GENRE, genre);



		// then - add genreID to DISCID with & as delimiter 

		String newDISCID = cd.getAlbom_id() + "&" + genreID;

		

		// then add performer and get it's id

		int performerID = add_Genre_or_Artist(ReferedObject.ARTIST, cd.getPerformer());
	

		// then add cd to Cds table

		// in case this cd (meaning this DISCID (= newDISCID) ) is already in Data Base => we won't change anything (=> DISCID= newDISCID)
		String insert = "INSERT INTO Cds(DISCID,cdTitle,cdPerformer,genreID,cdLengthInSec, cdYear) VALUES(?,?,?,?,?,?) " +
				" ON DUPLICATE KEY UPDATE DISCID = ? " ; 

		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert);

			pstmt.setString(1,newDISCID);

			pstmt.setString(2,cd.getTitle());
			pstmt.setInt(3,performerID);

			pstmt.setInt(4, genreID);
			pstmt.setInt(5, cd.getLengthInSec());
			pstmt.setInt(6, cd.getReleaseYear());

			pstmt.setString(7,newDISCID);




			pstmt.executeUpdate();

			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in addCD, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured. Cd wasn't added");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}

		// now - batch tracks

		int numOfTracks = cd.getTracks().size();

		ArrayList<String> songNames = new ArrayList<String>();

		ArrayList<String> artists = new ArrayList<String>();

		ArrayList<String> trackIDs = new ArrayList<String>();

		int[] artistIDs = new int[numOfTracks];

		int[] songIDs = new int[numOfTracks];

		ArrayList<Integer> trackLengths = new ArrayList<Integer>();



		for(int i=0; i< numOfTracks; i++)
		{
			songNames.add( cd.getTracks().get(i).getTitle()) ;

			artists.add( cd.getTracks().get(i).getArtistName()) ;
			trackIDs.add(newDISCID + "-" + cd.getTracks().get(i).getTrackNum());
			trackLengths.add(cd.getTracks().get(i).getLengthInSec()) ;
		}


		// first - get artists IDs

		artistIDs = batchArtists(artists);
		System.out.println(artists.size()+" artists were batched");


		// then - insert songs and get songIDs - 

		songIDs = batchSongs(songNames, artistIDs);
		System.out.println(artistIDs.length+" songs were batched");
		batchLyrics(songIDs, cd.getLyrics());


		// then add tracks

		batchTracks(trackIDs, songIDs, trackLengths);
		System.out.println(trackIDs.size()+" tracks were batched");



			}


	//==================================================================================

	
	/**
	 * Add song titles to Songs table and get their generated keys
	 * @throws SQLException 
	 * 
	 */
	
	private synchronized int[] batchSongs(ArrayList<String> song_names,int[] artistIDs) throws SQLException 
			
			{




		PreparedStatement	pstmt = null;

		int[] songIDs =new int[song_names.size()];

		int numOfSongs = song_names.size();

		ResultSet rs = null;

		String insert=	"INSERT INTO Songs(artist_ID,songName) VALUES(?, ?) ON DUPLICATE KEY UPDATE update_flag = not update_flag";

		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert,new String[]{"ID"});

			int rest = numOfSongs;

			int k=0;
			int m=0;
			int l=0;

			while(rest>0)

			{

				pstmt.clearBatch();
				k = Math.min(BATCH_SIZE,rest);



				for (int i=0; i< k; i++)
				{

					pstmt.setInt(1,artistIDs[l]);
					pstmt.setString(2,song_names.get(l));
					pstmt.addBatch();			
					pstmt.executeUpdate();

					l++;
				
				}



				pstmt.executeBatch();

				rest = rest - k;


				rs	= pstmt.getGeneratedKeys();
				conn.commit();


				while (rs.next() == true)
				{
					songIDs[m] = rs.getInt(1);
					m++;

				}


			}




		}	
		catch (SQLException e)
		{
			System.out.println("Exception in batchSongs, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured while inserting songs. Update was corrupted!!");

		}


		finally 
		{

			rs.close();
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}	

		}


		return  songIDs;


			}


	//==================================================================================


	/**
	 * Add cds to Cds table
	 * @throws SQLException 
	 */

	private synchronized void batchCDs(ArrayList<String> DISCIDs, String[]cdTitles, int[] cdPerformerIDs, 
			int[] genreIDs, int[] cdLengths, int[] cdYears) throws SQLException  

			{


		PreparedStatement	pstmt = null;


		int numOfCds = DISCIDs.size();



		String insert=	"INSERT INTO Cds(DISCID,cdTitle,cdPerformer,genreID,cdLengthInSec, cdYear) VALUES(?,?,?,?,?,?) " +
				" ON DUPLICATE KEY UPDATE cdTitle = ?, cdPerformer = ?, cdLengthInSec = ?, cdYear =?";

		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert);
			int rest = numOfCds;

			int k=0;
			int l=0;

			while(rest>0)

			{
				pstmt.clearBatch();
				k = Math.min(BATCH_SIZE,rest);

				for (int i=0; i< k; i++)
				{

					pstmt.setString(1,DISCIDs.get(l));

					pstmt.setString(2,cdTitles[l]);
					pstmt.setInt(3,cdPerformerIDs[l]);

					pstmt.setInt(4, genreIDs[l]);
					pstmt.setInt(5, cdLengths[l]);
					pstmt.setInt(6, cdYears[l]);

					pstmt.setString(7,cdTitles[l]);
					pstmt.setInt(8,cdPerformerIDs[l]);
					pstmt.setInt(9, cdLengths[l]);
					pstmt.setInt(10, cdYears[l]);
					pstmt.addBatch();			
					pstmt.executeUpdate();

					l++;
				


				}

				pstmt.executeBatch();


				rest = rest -k;

				conn.commit();


			}



		}	
		catch (SQLException e)
		{
			System.out.println("Exception in batchCDs, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured while inserting CDs. Update was corrupted!!");

		}


		finally 
		{


			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}	

		}


			}

	//==================================================================================


	/**
	 * Add genres to Genres table and get their generated keys
	 * @throws SQLException 	
	 */

	private synchronized int[] batchGenres(String[] genres) throws SQLException

	{


		PreparedStatement	pstmt = null;

		int[] genreIDs =  new int[genres.length];

		int numOfGenres = genres.length;

		ResultSet rs = null;

		String insert=	"INSERT INTO Genres(genreName) VALUES(?) ON DUPLICATE KEY UPDATE update_flag = not update_flag";

		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert,new String[]{"ID"});

			for (int i=0; i< numOfGenres; i++)
			{

				pstmt.setString(1,genres[i]);
			

				pstmt.addBatch();			
				pstmt.executeUpdate();
			}

			pstmt.executeBatch();
			rs	= pstmt.getGeneratedKeys();
			conn.commit();

			int i=0;

			while (rs.next() == true)
			{
				if (i<genreIDs.length)
				{
					genreIDs[i] = rs.getInt(1);
					i++;
				}

			}


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in batchGenres, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured while inserting Genres. Update was corrupted!!");

		}


		finally 
		{

			rs.close();
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}	

		}



		return  genreIDs;


	}


	//==================================================================================


	/**
	 * Add artist to Artists table and get their generated keys
	 * @throws SQLException 	
	 */

	private synchronized int[] batchArtists(ArrayList<String> artistNames) throws SQLException 

	{



		PreparedStatement	pstmt = null;

		int[] artistIDs =new int[artistNames.size()];

		int numOfArtists = artistNames.size();

		System.out.println("numOfArtists = " + numOfArtists);

		ResultSet rs = null;

		String insert=	"INSERT INTO Artists(artistName) VALUES(?) ON DUPLICATE KEY UPDATE update_flag = not update_flag";

		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert,new String[]{"ID"});



			int rest = numOfArtists;

			int k=0;

			int m=0; 
			int l=0;

			while(rest>0)

			{
				pstmt.clearBatch();
				k = Math.min(BATCH_SIZE,rest);

				for (int i=0; i< k; i++)
				{

					pstmt.setString(1,artistNames.get(l));
					pstmt.addBatch();			
					pstmt.executeUpdate();
					l++;
			

				}


				pstmt.executeBatch();

				rest = rest -k;


				rs	= pstmt.getGeneratedKeys();
				conn.commit();



				while (rs.next() == true)
				{
					artistIDs[m] = rs.getInt(1);
					m++;

				}


			}



		}	
		catch (SQLException e)
		{
			System.out.println("Exception in batchArtists, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured while inserting Artists. Update was corrupted!!");

		}


		finally 
		{

			rs.close();
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}	

		}





		return  artistIDs;


	}
	//==================================================================================

	/**
	 * Add lyrics to Lyrics table
	 * @throws SQLException 
	 */
	
	private synchronized void batchLyrics(int[] songIDs, ArrayList<String> lyrics) throws SQLException 
			
			{



		PreparedStatement	pstmt = null;

		int numOfSongs = songIDs.length;


		String insert=	"INSERT INTO Lyrics(songID,lyrics) VALUES(?, ?) ON DUPLICATE KEY UPDATE lyrics = ?";

		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert);

			for (int i=0; i< numOfSongs; i++)
			{
				String lyric = lyrics.get(i);
				if (lyric != null)
				{
					pstmt.setInt(1,songIDs[i]);
					pstmt.setString(2,lyric);
					pstmt.setString(3,lyric);					
					pstmt.addBatch();			
					pstmt.executeUpdate();
				}

				else
				{
					continue;
				}
			}

			pstmt.executeBatch();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in batchLyrics, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			throw new SQLException("ERROR: Exception accured while inserting Lyrics. Update was corrupted!!");

		}


		finally 
		{


			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}	

		}



			}



	//==================================================================================



	private String getDiscIdfromTrackID(String trackID)
	{
		return trackID.substring(0, trackID.indexOf('-'));
	}
	//==================================================================================



	/**
	 * Add new(!) tracks to DB. trackID = DISCID&genreID-trackNum
	 * @throws SQLException 
	 * 
	 */

	private synchronized void batchTracks(ArrayList<String> trackIDs, int[] songIDs, ArrayList<Integer> trackLengths) 
			throws SQLException
	{

		String insert = "INSERT INTO Tracks(TrackID, DISCID ,songID,trackLengthInSec) VALUES(?,?,?,?) " +
				"ON DUPLICATE KEY UPDATE TrackID = TrackID" ;


		int numOfTracks = trackIDs.size();

		PreparedStatement	pstmt = null;



		try 
		{


			pstmt	=	conn.prepareStatement(insert);


			int rest = numOfTracks;

			int k=0;

			int l=0;

			while(rest>0)


			{
				pstmt.clearBatch();
				k = Math.min(BATCH_SIZE,rest);

				for (int i=0; i< k; i++)
				{

					String trackID_i = trackIDs.get(l);

					pstmt.setString(1,trackID_i);

					String DiscID = getDiscIdfromTrackID(trackID_i);

					pstmt.setString(2,DiscID);

					pstmt.setInt(3, songIDs[l]);

					pstmt.setInt(4, trackLengths.get(l));

					pstmt.addBatch();	

					pstmt.executeUpdate();

					l++;
	
				}


				rest = rest -k;
				pstmt.executeBatch();


			}
		}	



		catch (SQLException e)
		{
			System.out.println("Exception in batchTracks, transaction is not complete: Exception: " +  e.toString() );
			throw new SQLException(e.getMessage());

		}




		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();

			}

		}




	}


	//==================================================================================

	/**
	 *  
	 * @return new generated id
	 * @throws SQLException  
	 * 
	 */


	private synchronized int add_Genre_or_Artist(ReferedObject refObj, String whatToAdd) throws SQLException {

		String table = "";

		String columnName = "";
		String IDName = "";

		switch (refObj)
		{
		case ARTIST:
			table += " Artists ";
			columnName += " artistName ";
			IDName += " artistID ";
			break;


		case GENRE:
			table += " Genres ";
			columnName += " genreName ";
			IDName += " genreID ";
			break;



		}

		PreparedStatement	pstmt = null;

		ResultSet		rs = null;

		int id = 0;


		String insert = " INSERT INTO " + table + "(" + columnName + ")" + 
				" VALUES(?) ON DUPLICATE KEY UPDATE " + columnName + "=" +columnName;



		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert, new String[]{"ID"});

			pstmt.setString(1,whatToAdd);

			pstmt.executeUpdate();



			rs	= pstmt.getGeneratedKeys();
			conn.commit();

		}	
		catch (SQLException e)
		{
			System.out.println("Exception in add_Genre_or_Artist, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured. Update was corrupted!!");

		}


		finally 
		{
			if (pstmt != null) 
			{

				if ( rs.next() == true)
				{
					id =  rs.getInt(1);

				}

				else
				{



					String query1 = " SELECT " + IDName + " FROM " + table + " WHERE " + columnName + " = ?";
					pstmt = conn.prepareStatement(query1);
					pstmt.setString(1, whatToAdd);

					rs	= pstmt.executeQuery();
					rs.next();

					id = rs.getInt(1);
					pstmt.close();
				}
				rs.close();
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}



		return id;


	}

	//==================================================================================
	/**
	 * adds tracks to specific cd
	 * @throws SQLException 
	 * 
	 */


	public synchronized void addTracksToDisc(ArrayList<Track> changedCdTracks) throws SQLException 
			

			{
		ArrayList<String> trackTitles = new ArrayList<String>();
		ArrayList<String> trackArtists = new ArrayList<String>();
		ArrayList<Integer> trackLengths = new ArrayList<Integer>();
		ArrayList<String> trackIDs = new ArrayList<String>();

		for (int j=0; j< changedCdTracks.size(); j++)
		{
			trackTitles.add(changedCdTracks.get(j).getTitle());
			trackArtists.add(changedCdTracks.get(j).getArtistName());
			trackLengths.add(changedCdTracks.get(j).getLengthInSec());	
			trackIDs.add(changedCdTracks.get(j).getDisk_id() + "-" +  changedCdTracks.get(j).getTrackNum());

		}


		int[] trackArtistsIDs = batchArtists(trackArtists);

		int[] trackTitlesIDs = batchSongs(trackTitles, trackArtistsIDs);
		batchTracks(trackIDs, trackTitlesIDs, trackLengths);

			}



	//==================================================================================



	/**
	 * 
	 * songID should be in DB already
	 * lyrics is the lyrics itself and not the file path of it
	 * this song shouldn't have lyrics in DB yet
	 * 
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized void addLyrics(int songID, String lyrics) throws SQLException 

	{


		PreparedStatement	pstmt = null;



		String insert = "INSERT INTO Lyrics (songID, lyrics) VALUES(?,?) ON DUPLICATE KEY UPDATE " +
				" lyrics = ?";


		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert);

			pstmt.setInt(1, songID);
			pstmt.setString(2,lyrics);
			pstmt.setString(3,lyrics);

			pstmt.executeUpdate();

			conn.commit();




		}	
		catch (SQLException e)
		{
			System.out.println("Exception in adding Lyrics, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");

			throw new SQLException("ERROR: Exception in adding Lyrics, Update was corrupted!!");

		}

		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//==================================================================================


	/**
	 * DISCID includes genre id
	 * one or more of cdTitle, cdYear, performer, length maybe empty (for example - if admin wants to change only CD title)
	 * DISCID should be in DB already
	 * 
	 * editing performer - means changing cd's performer (e.g. changes like madona to beatles)
	 * if you want to edit performer (e.g. madonna instead of madona) use method edit
	 * 
	 * @throws -SQLException
	 * 
	 */



	public synchronized void editCD(String DISCID, String cdTitle, int cdYear, String performer, int cdlength) throws SQLException 

	{

		String update = "UPDATE Cds SET " ;
		int numOfNotEmptyParams = 0;
		int i =0;

		int set_title = 0;
		int set_year = 0;
		int set_artistID = 0;
		int set_length = 0;



		if(cdTitle != null)
		{
			update += " cdTitle = ? ";
			numOfNotEmptyParams++;
			set_title = ++i;

		}


		int artistID = 0;
		if (performer != null)
		{
			// add this artist ant get it's ID
			artistID = add_Genre_or_Artist(ReferedObject.ARTIST, performer);

			if (numOfNotEmptyParams == 0)
			{

				update += " cdPerformer = ? ";

			}

			else
			{
				update += " , cdPerformer = ? ";

			}
			numOfNotEmptyParams++;
			set_artistID = ++i;

		}


		if(cdYear != 0)
		{
			if (numOfNotEmptyParams == 0)
			{
				update += " cdYear = ? ";

			}

			else
			{
				update += ", cdYear = ? ";

			}

			numOfNotEmptyParams++;
			set_year = ++i;

		}


		if(cdlength != 0)
		{
			if (numOfNotEmptyParams == 0)
			{
				update += " cdLengthInSec = ? ";

			}

			else
			{
				update += ", cdLengthInSec = ? ";

			}

			numOfNotEmptyParams++;
			set_length = ++i;
		}



		++i;

		update += " WHERE DISCID = ?";



		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(update);


			if(set_title != 0)
			{
				pstmt.setString(set_title,cdTitle);

			}


			if(set_length != 0)
			{
				pstmt.setInt(set_length, cdlength);

			}


			if (set_artistID != 0)
			{
				pstmt.setInt(set_artistID, artistID);
			}

			if (set_year != 0)
			{
				pstmt.setInt(set_year, cdYear);
			}



			pstmt.setString(i,DISCID);

			System.out.println("the auery in edit cd is "+ update);
			pstmt.executeUpdate();

			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception while editing CD, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException(e.getMessage());
		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}
	}


	//=====================================================================================================


	/**
	 * only mistake editing!!! 
	 * genre should be already in DB
	 * @throws SQLException 

	 * 
	 */
	public synchronized void editGenre(String genre , String newGenre) throws SQLException 

	{

		String update = "UPDATE Genres SET genreName = ? WHERE genreName = ?" ;


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(update);

			pstmt.setString(1,newGenre);

			pstmt.setString(2, genre);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in editGenre, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERORR: No changes were made, such object already exists in DB");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//=====================================================================================================


	
	/**
	 * only mistake editing!!!
	 * edit Artist or Song or Lyrics
	 * targetID should be already in table represented by target
	 * @throws SQLException 
	 * 
	 */


	public synchronized void edit(int refObjID ,ReferedObject refObj, String newData) throws SQLException 

	{


		String table = "";
		String columnName = "";
		String IDName = "";
		switch (refObj)
		{
		case ARTIST:
			table += " Artists ";
			columnName += " artistName ";
			IDName += " artistID ";
			break;

		case SONG:
			table += " Songs ";
			columnName += " songName ";
			IDName += " songID ";
			break;



		case LYRIC:
			table += " Lyrics ";
			columnName += " lyrics ";
			IDName += " songID ";
			break;

		}


		String update = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + IDName + " = ?" ;


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(update);

			pstmt.setString(1,newData);

			pstmt.setInt(2, refObjID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in edit (target = " + table + " ), transaction is not complete: Exception: " +  e.toString() );

			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("\nERORR: No changes were made, such object already exists in DB");


		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//==================================================================================



	/**
	 * DISCID includes genre id
	 * DISCID should be in DB already
	 * this will also delete all the tracks in that cd       
	 * 
	 * @throws - throws SQLException  
	 * 
	 */


	public synchronized void deleteCD(String DISCID) throws SQLException 

	{
		String delete = "DELETE FROM Cds WHERE DISCID = ?";



		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setString(1,DISCID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception while deleting CD, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while deleting CD. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}



	}

	//==================================================================================
	/**
	 * deletes empty cds from database
	 * @throws SQLException  
	 * 
	 */
	
	public synchronized void deleteEmptyCds() throws SQLException 

	{
		ArrayList<String> cdsToDelete  = new ArrayList<String>();
		String query1 =" SELECT  DISCID From empty_cd_finder WHERE numOfCdTracks	= ? " ;

		PreparedStatement	pstmt = conn.prepareStatement(query1);
		pstmt.setInt(1,0);


		ResultSet	rs		=	pstmt.executeQuery();

		boolean next = rs.next();
		if(next){
			while(next==true){
				cdsToDelete.add(rs.getString(1));
				next = rs.next();
			}




			String delete_empty_cds = " DELETE FROM Cds WHERE DISCID = ? ";

			pstmt = null;

			try 
			{

				conn.setAutoCommit(false);

				pstmt	=	conn.prepareStatement(delete_empty_cds);

				int numofCDsTodelete = cdsToDelete.size();


				for (int i=0; i< numofCDsTodelete; i++)
				{


					pstmt.setString(1,cdsToDelete.get(i));
					pstmt.addBatch();			
					pstmt.executeUpdate();


				}



				pstmt.executeBatch();



				conn.commit();


			}	
			catch (SQLException e)
			{
				System.out.println("Exception while deleting empty CDs, transaction is not complete: Exception: " +  e.toString() );
				System.out.println("Transaction is being rolled back...");
				conn.rollback();
				System.out.println("Rollback Successfully :)");
				throw new SQLException("ERROR: Exception while deleting empty CDs. No changes were made");

			}


			finally 
			{
				if (pstmt != null) 
				{
					pstmt.close();
					conn.setAutoCommit(true);
				}

			}

		}

	}

	//==================================================================================


	/**
	 * 
	 * artistId should be in DB already
	 * 
	 * this will also delete all songs (which may cause deleting other tracks, comments and lyrics as well), 
	 * tracks and cds by this artist
	 * 
	 * @throws - throws SQLException  
	 * 
	 */
	
	public synchronized void deleteArtist(int artistID) throws SQLException 
	{


		String delete = "DELETE FROM Artists WHERE artistID = ?";




		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setInt(1,artistID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in deleteSongwhile deleting Artist, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while deleting Artist. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//==================================================================================




	/**
	 * deletes track from a specific cd
	 * (meaning, this song can stay in the database, but only not in this cd)
	 * trackID should be in DB already
	 * trackID combined from DISCID (which includes genre id) and track number in CD
	 * 
	 * @throws - throws SQLException
	 * 
	 */


	public synchronized void deleteTrack(String trackID) throws SQLException 
	{



		String delete = "DELETE FROM Tracks WHERE TrackID = ?";



		PreparedStatement	pstmt = null;


		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setString(1,trackID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception while deleting Track, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while deleting Track. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//==================================================================================

	
	/**
	 * deletes all tracks from a specific cd
	 * trackID should be in DB already
	 * trackID combined from DISCID (which includes genre id) and track number in CD
	 * 
	 * @throws - throws SQLException
	 * 
	 */
	

	public synchronized void deleteAllTracks(String DISCID) throws SQLException
	{
		String delete = "DELETE FROM Tracks WHERE DISCID = ?";


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setString(1, DISCID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in deleteALLTracks, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while editing CD. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}



	//==================================================================================

	/**
	 * deletes song from all cds containing it. 
	 * If the artist of that song doesn't have any other songs, it will be deleted as well
	 * songID should be in DB already
	 * 
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized void deleteSong(int songID) throws SQLException 
	{
		// first: get the artsitID of that song, 
		// since in case that after deleting this song there won't be any songs by that artist, you'll have to delete it



		String query1 ="SELECT  artist_ID From Songs WHERE songID = ? " ;

		PreparedStatement	pstmt = conn.prepareStatement(query1);
		pstmt.setInt(1, songID);

		ResultSet	rs		=	pstmt.executeQuery();

		rs.first();
		int artistID = rs.getInt(1);


		pstmt=null;

		// then delete the song (which will automatically delete the track)

		String delete = "DELETE FROM Songs WHERE songID = ?";



		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setInt(1,songID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while deleting Song. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


		//then check if there are any songs left by that artist

		String query2 ="SELECT  artist_ID From Songs WHERE artist_ID = ?";

		pstmt	=	conn.prepareStatement(query2);
		pstmt.setInt(1, artistID);

		rs		=	pstmt.executeQuery();


		// if there aren't any songs by that artist => delete the artist
		if (rs.next() == false)
		{
			deleteArtist(artistID);

		}


		pstmt.close();
		rs.close();

	}


	//==================================================================================


	/**
	 * 
	 * genre should be in DB already
	 * 
	 * This will automatically delete all cds from that genre!!!!
	 * 
	 * @throws - throws SQLException  
	 * 
	 */

	public synchronized void deleteGenre(String genre) throws SQLException 
	{

		String delete = "DELETE FROM Genres WHERE genreName = ?";


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setString(1,genre);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in deleteGenre, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while deleting Genre. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}



	}

	//==================================================================================



	/**
	 * 
	 * songID should be in DB already
	 * 
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized void deleteLyrics(int songID) throws SQLException 
	{

		String delete = "DELETE FROM Lyrics WHERE songID = ?";


		PreparedStatement	pstmt = null;


		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setInt(1,songID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in deleteLyrics, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while deleting Lyrics. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}



	}


	//==================================================================================



	/////////////////////////////////////////////////////////////////
	/////////////////       USER METHODS       //////////////////////
	/////////////////////////////////////////////////////////////////


	/**
	 *  user_name shouldn't be already in DB
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized int addUser(String user_name, String user_pass) throws SQLException 

	{
		PreparedStatement	pstmt = null;

		int id=0;

		String insert = "INSERT INTO Users (userName, userPassword) VALUES(?,?)";



		try 
		{

			conn.setAutoCommit(false);

			pstmt	=	conn.prepareStatement(insert,new String[]{"ID"});

			pstmt.setString(1,user_name);
			pstmt.setString(2,user_pass);

			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();
			conn.commit();
			
			rs.first();
			id = rs.getInt(1);




		}	
		catch (SQLException e)
		{
			System.out.println("Exception in addUser, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while adding new user. The user wasn't added");

		}

		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}
		return id;

	}


	//=====================================================================================================




	/**
	 * userName should be already in DB and shouldn't be admin!!
	 * 
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized void deleteUser(String userName) throws SQLException 

	{
		String delete = "DELETE FROM Users WHERE userName = ?";

		PreparedStatement	pstmt = null;


		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setString(1,userName);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in deleteUser, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while deleting user. The user wasn't deleted");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}




	}


	//=====================================================================================================

	/**
	 * userName should be already in DB
	 * this method should be called only(!!) after user logged in
	 * 
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized void changePass(String userName, String new_user_pass) throws SQLException 
	{
		String update = "UPDATE Users SET userPassword = ? WHERE userName = ?";



		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(update);

			pstmt.setString(1,new_user_pass);

			pstmt.setString(2, userName);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in changePass, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception while changing password. The password wasn't changed");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}



	}


	//=====================================================================================================

		
	/**
	 * 
	 * @return user's name and it's password
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized User findUser(String user_name) throws SQLException {

		User user = null;


		String query = 

				"SELECT   userID, userName, userPassword " +

					" FROM Users " +

					" WHERE userName = ? ";


		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		pstmt.setString(1, user_name);
		ResultSet	rs		=	pstmt.executeQuery();

		if (rs.first() == true)
		{

			user=new User(rs.getInt("userID"), rs.getString("userName"),rs.getString("userPassword"));

		}


		// closing
		rs.close();
		pstmt.close();



		return user;
	}



	//=====================================================================================================


	/////////////////////////////////////////////////////////////
	/////////////////       Likings Methods        //////////////
	/////////////////////////////////////////////////////////////



	/**
	 * target = Cd or Artist or Song ( see Target enum)
	 * 
	 * if target isn't Cd, then targetID is originally int, so we'll change it back to int
	 * if target is Cd, then it includes genre id as well
	 * targetID and user_id should be already in DB
	 * 
	 * l = LIKE for liking and l=UNLIKE for unliking
	 * 
	 * @throws - throws SQLException  
	 * 
	 */

	public synchronized void like_UnLike(String targetID,Target target, Like l,  int user_id) throws SQLException 
	{

		String table = "";
		int ID = 0;

		String idName = "";

		String query = "";


		switch (target)
		{
		case ARTIST:
			table += " Artists_Likes ";
			ID = Integer.parseInt(targetID);
			idName += " artistID ";
			break;

		case SONG:
			table += " Songs_Likes ";
			ID = Integer.parseInt(targetID);
			idName += " songID ";
			break;	

		case CD:
			table += " CDs_Likes ";
			idName += " DISCID ";
			break;



		}

		switch(l)
		{
		case LIKE:
			query+= " INSERT INTO " + table + "(" + idName +" , userID) VALUES(?,?) ";	
			break;

		case UNLIKE:
			query +=" DELETE FROM " + table + " WHERE " + idName + " = ? AND userID = ? ";	
			break;
		} 



		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(query);


			if ((target == Target.ARTIST) || (target == Target.SONG))
			{
				pstmt.setInt(1,ID);
			}

			else 
			{
				pstmt.setString(1, targetID);
			}


			pstmt.setInt(2, user_id);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in like_UnLike, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//=====================================================================================================

	

	/** target = Cd or Artist or Song (see the enum - Target)
	 * if target isn't Cd, then targetID is originally int, so we'll change it back to int
	 * if target is Cd, then it includes genre id as well
	 * targetID should be already in DB
	 * @return target_id and NumOfCDRatings
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized int getNumOfLikes(String target_id, Target target) throws SQLException {

		PreparedStatement	pstmt= null;
		int numOfLikes = 0;
		String table = "";
		int ID = 0;

		String idName = "";

		switch (target)
		{
		case ARTIST:
			table += "Artists_Likes";
			ID = Integer.parseInt(target_id);
			idName += "artistID";
			break;

		case SONG:
			table += "Songs_Likes";
			ID = Integer.parseInt(target_id);
			idName += "songID";
			break;	

		case CD:
			table += "CDs_Likes";
			idName += "DISCID";
			break;

		}


		String query = 
				" SELECT count(*) AS num_Of_Likes FROM " + 	table + 
				" WHERE " + table + "." + idName + " = ? ";	

			pstmt	=	conn.prepareStatement(query);

		if ((target == Target.ARTIST) || (target == Target.SONG))
		{
			pstmt.setInt(1, ID);
		}

		else 
		{
			pstmt.setString(1, target_id);
		}




		ResultSet	rs		=	pstmt.executeQuery();


		if (rs.first() == true)
		{

			numOfLikes = rs.getInt(1);
		}


		// closing
		rs.close();
		pstmt.close();


		return numOfLikes;
	}

	//=====================================================================================================


	/**
	 * target = Cd or Artist or Song (see the enum - Target)
	 * if target isn't Cd, then targetID is originally int, so we'll change it back to int
	 * if target is Cd, then it includes genre id as well
	 * targetID should be already in DB
	 * @return true if user liked the object represented by target and target_id and false otherwise
	 * @throws - throws SQLException  
	 * 
	 */
	
	
	public synchronized boolean isUserLike(String target_id, Target target, int userID) throws SQLException
	{
		boolean b = false;


		String table = "";
		int ID = 0;

		String idName = "";

		switch (target)
		{
		case ARTIST:
			table += " Artists_Likes ";
			ID = Integer.parseInt(target_id);
			idName += " artistID ";
			break;

		case SONG:
			table += " Songs_Likes ";
			ID = Integer.parseInt(target_id);
			idName += " songID ";
			break;	

		case CD:
			table += " CDs_Likes ";
			idName += " DISCID ";
			break;

		}


		String query = 
				" SELECT userID FROM " + 	table + 
				" WHERE " + table + "." + idName + "= ? "+	
				" AND " + table + ".userID" + "= ? ";

		PreparedStatement	pstmt	=	conn.prepareStatement(query);

		if ((target == Target.ARTIST) || (target == Target.SONG))
		{
			pstmt.setInt(1, ID);
		}

		else 
		{
			pstmt.setString(1, target_id);
		}


		pstmt.setInt(2, userID);


		ResultSet	rs		=	pstmt.executeQuery();



		if (rs.first() == true)
		{

			b = true;
		}


		// closing
		rs.close();
		pstmt.close();



		return b;

	}


	//=====================================================================================================

	////////////////////////////////////////////////////////////////////
	/////////////////       Comments Methods      //////////////////////
	////////////////////////////////////////////////////////////////////


	/**
	 * 
	 * songID and user_id should be already in DB
	 * DATETIME  is from this format 'YYYY-MM-DD HH:MM:SS'
	 *
	 * @throws - throws SQLException  
	 * 
	 */

	public synchronized void addSongComment(int songID, int user_id, String DATETIME, String comment ) throws SQLException
	{

		String insert = "INSERT INTO Song_Comments(userID, songID, time, comment) VALUES(?,?,?,?)";


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(insert);


			pstmt.setInt(1, user_id);

			pstmt.setInt(2, songID);

			pstmt.setString(3, DATETIME);

			pstmt.setString(4, comment);

			pstmt.executeUpdate();

			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in addSongComment, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}



	}

	//=====================================================================================================



	/**
	 * 
	 * commentID should be already in DB
	 * DATETIME  is from this format 'YYYY-MM-DD HH:MM:SS'
	 *
	 * @throws - throws SQLException  
	 * 
	 */

	public synchronized void editSongComment(int commentID,String new_DATETIME, String new_comment ) throws SQLException

	{
		String update = "UPDATE Song_Comments SET time = ?, comment=? WHERE idSong_Comments = ?";


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(update);

			pstmt.setString(1,new_DATETIME);

			pstmt.setString(2,new_comment);

			pstmt.setInt(3, commentID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in editSongComment, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//=====================================================================================================



	/**
	 * 
	 * commentID should be already in DB
	 * @throws - throws SQLException  
	 * 
	 */

	public synchronized void deleteSongComment(int commentID ) throws SQLException
	{

		String delete = "DELETE FROM Song_Comments WHERE idSong_Comments = ?";


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(delete);

			pstmt.setInt(1,commentID);

			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in deleteSongComment, transaction is not complete: Exception: " +  e.toString() );
			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");
			throw new SQLException("ERROR: Exception accured. No changes were made");

		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}

	//=====================================================================================================
	
	
	/**
	 * 
	 * songID should be already in DB
	 * @return userName-comment-DATETIME "combinations"
	 *
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized ArrayList<Comment> getSongComments(int songID) throws SQLException {

		ArrayList<Comment> comments = new ArrayList<Comment>();
		Comment comment = null;
		User user = null;

		String query = 
				"SELECT idSong_Comments, time, comment, Users.userID, userName, userPassword " +
						" FROM Song_Comments, Users "  + 
						" WHERE Song_Comments.songID = ? " +
						" AND Users.userID = Song_Comments.userID " +
						" ORDER BY time DESC";	


		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		pstmt.setInt(1, songID);
		ResultSet	rs		=	pstmt.executeQuery();


		while (rs.next() == true)
		{

			user = new User(rs.getInt("userID"), rs.getString("userName"), rs.getString("userPassword"));
			comment = new Comment(rs.getInt("idSong_Comments"), rs.getString("comment"), user, rs.getString("time"));
			comments.add(comment);
		}


		// closing
		rs.close();
		pstmt.close();


		return comments;
	}


	//=====================================================================================================


	/////////////////////////////////////////////////////////////////
	/////////////////       Views  Methods     //////////////////////
	/////////////////////////////////////////////////////////////////



	/**
	 * target = Cd or Artist or Song ( see Target enum)
	 * 
	 * if target isn't Cd, then targetID is originally int, so we'll change it back to int
	 * if target is Cd, then it includes genre name as well
	 * targetID should be already in DB
	 * 
	 * @throws - throws SQLException  
	 * 
	 */

	public synchronized void addView(String targetID, Target target) throws SQLException 
	{
		String table = "";
		int ID = 0;

		String idName = "";

		switch (target)
		{
		case ARTIST:
			table += " Artists ";
			ID = Integer.parseInt(targetID);
			idName += " artistID ";
			break;

		case SONG:
			table += " Songs ";
			ID = Integer.parseInt(targetID);
			idName += " songID ";
			break;	

		case CD:
			table += " Cds ";
			idName += " DISCID ";
			break;

		}


		String update = " UPDATE " + table + " SET numOfViews = numOfViews +1  WHERE " + idName + " = ? " ;


		PreparedStatement	pstmt = null;

		try 
		{

			conn.setAutoCommit(false);
			pstmt	=	conn.prepareStatement(update);


			if ((target == Target.ARTIST) || (target == Target.SONG))
			{
				pstmt.setInt(1,ID);
			}

			else 
			{
				pstmt.setString(1, targetID);
			}


			pstmt.executeUpdate();


			conn.commit();


		}	
		catch (SQLException e)
		{
			System.out.println("Exception in addView, transaction is not complete: Exception: " +  e.toString() );

			System.out.println("Transaction is being rolled back...");
			conn.rollback();
			System.out.println("Rollback Successfully :)");


		}


		finally 
		{
			if (pstmt != null) 
			{
				pstmt.close();
				conn.setAutoCommit(true);
			}

		}


	}


	//=====================================================================================================

	
	/** target = Cd or Artist or Song (see the enum - Target)
	 * f target isn't Cd, then targetID is originally int, so we'll change it back to int
	 * if target is Cd, then it includes gente id as well
	 * targetID should be already in DB
	 * @return NumOfViews
	 * @throws - throws SQLException  
	 * 
	 */
	public synchronized int getNumOfViews(String target_id, Target target) throws SQLException {
		int numOfViews = 0;
		String table = "";
		int ID = 0;

		String idName = "";

		switch (target)
		{
		case ARTIST:
			table += " Artists ";
			ID = Integer.parseInt(target_id);
			idName += " artistID ";
			break;

		case SONG:
			table += " Songs ";
			ID = Integer.parseInt(target_id);
			idName += " songID ";
			break;	

		case CD:
			table += " Cds ";
			idName += " DISCID ";
			break;

		}


		String query = 
				" SELECT numOfViews " +
						" FROM " + 	table + 
						" WHERE " + table + "." + idName + "= ? ";	

		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		if ((target == Target.ARTIST) || (target == Target.SONG))
		{
			pstmt.setInt(1, ID);
		}

		else 
		{
			pstmt.setString(1, target_id);
		}


		
		ResultSet	rs		=	pstmt.executeQuery();


		if (rs.first() == true)
		{

			numOfViews = rs.getInt(1);
		}


		// closing
		rs.close();
		pstmt.close();


		return numOfViews;
	}

	//=====================================================================================================

	
	/**
	 * 
	 * 
	 * @return TOP5 viewed Cds - id, title, performer, year,genre
	 * @throws SQLException 
	 *  
	 * 
	 */
	public synchronized ArrayList<Cd> getTOP_5_CDs() throws SQLException {



		ArrayList<Cd> cds = new  ArrayList<Cd>();
		Cd cd = null;



		String query = 
				"SELECT DISCID, cdTitle, cdLengthInSec, " +
						" cdPerformer, cdYear, genreName, artistName " +
						" FROM Cds, Genres, Artists " + 
						" WHERE Genres.genreID = Cds.genreID AND Artists.artistID = Cds.cdPerformer " + 
						" ORDER BY Cds.numOfViews DESC " +
						" LIMIT 0,5";


		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		ResultSet	rs		=	pstmt.executeQuery();


		while (rs.next() == true)
		{

			cd=new Cd(rs.getString("cdTitle"), rs.getString("DISCID"), rs.getInt("cdPerformer"),rs.getString("artistName") ,
					rs.getInt("cdYear"), rs.getInt("cdLengthInSec"), rs.getString("genreName"), 0, 0); 
			// we don't need here views and likes


			cds.add(cd);
		}


		// closing
		rs.close();
		pstmt.close();


		return cds;
	}


	//=====================================================================================================

	

	/**
	 * 
	 * 
	 * @return TOP5 viewed artists - ids, names
	 * @throws SQLException  
	 * 
	 */
	public synchronized ArrayList<Artist> getTOP_5_Artists() throws SQLException 
	{
		ArrayList<Artist> Artists = new ArrayList<Artist>();
		Artist artist;


		String query ="SELECT  artistID, artistName " +
				" FROM Artists WHERE artistName != 'Various' " +
				" ORDER BY Artists.numOfViews DESC " +
				" LIMIT 0,5 ";
		
 
		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		ResultSet	rs		=	pstmt.executeQuery();


		while (rs.next() == true)
		{

			artist = new Artist(rs.getInt("artistID"), rs.getString("artistName"), 0, 0, null);
			// we don't need here cds, views and likes
			Artists.add(artist);

		}

		// closing
		rs.close();
		pstmt.close();

		return Artists;
	}



	//=====================================================================================================

	

	/**
	 * 
	 * 
	 * @return TOP5 viewed songs - ids, names
	 * @throws SQLException  
	 * 
	 */
	public synchronized ArrayList<Song> getTOP_5_Songs() throws SQLException 
	{
		ArrayList<Song> songs = new ArrayList<Song>();
		Song song =null;



		String query ="SELECT  songID, songName, artistName " +
				" FROM Songs, Artists " +
				" WHERE Artists.artistID = Songs.artist_ID " +
				" ORDER BY Songs.numOfViews DESC " +
				" LIMIT 0,5";


		PreparedStatement	pstmt	=	conn.prepareStatement(query);
		ResultSet	rs		=	pstmt.executeQuery();


		while (rs.next() == true)
		{

			song = new Song(rs.getInt("songID"), rs.getString("songName"), rs.getString("artistName"),null, null, 0, 0);
			// we don't need here lyrics, comments, views and likes
			songs.add(song);

		}

		// closing
		rs.close();
		pstmt.close();

		return songs;
	}



	//=====================================================================================================




	/**
	 * 
	 * "opens" the connection
	 */
	public void openConnection()
	{

		// loading the driver
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch (ClassNotFoundException e)
		{
			System.out.println("Unable to load the MySQL JDBC driver..");
			java.lang.System.exit(0); 
		}
		System.out.println("Driver loaded successfully");



		// creating the connection
		System.out.print("Trying to connect.. ");
		try
		{
			conn = DriverManager.getConnection(
					"jdbc:mysql://"+ host_name + ":" + port +  "/" + schema_name,user_name,user_pass);
		}
		catch (SQLException e)
		{
			System.out.println("Unable to connect - " + e.toString());
			java.lang.System.exit(0); 
		}
		System.out.println("Connected!");

	}
	//==================================================================================

	/**
	 * close the connection
	 */
	public void closeConnection()
	{
		// closing the connection
		try
		{			
			conn.close();
			System.out.println("connection closed successfully!");
		}
		catch (SQLException e)
		{
			System.out.println("Unable to close the connection - " + e.toString());
			java.lang.System.exit(0); 
		}
	}

}

